import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author YuanYuan
 * @Date 2022/11/26
 * @Time 10:46
 * +----------+-------------+------+-----+---------+----------------+
 * | Field    | Type        | Null | Key | Default | Extra          |
 * +----------+-------------+------+-----+---------+----------------+
 * | blogId   | int(11)     | NO   | PRI | NULL    | auto_increment |
 * | title    | varchar(50) | YES  |     | NULL    |                |
 * | content  | text        | YES  |     | NULL    |                |
 * | postTime | datetime    | YES  |     | NULL    |                |
 * | userId   | int(11)     | YES  |     | NULL    |                |
 * +----------+-------------+------+-----+---------+----------------+
 */
public class BlogDAO {
    //插入博客
    public static void insert(Blog blog) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "insert into blog values(null,?,?,now(),?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, blog.getTitle());
            preparedStatement.setString(2,blog.getContent());
            preparedStatement.setInt(3,blog.getUserId());
            int ret = preparedStatement.executeUpdate();
            if(0!=ret) {
                System.out.println("插入成功!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,null);
        }
    }
    //根据用户ID显示所有博客--博客列表
    public static List<Blog> selectAll() throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Blog> blogList = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from blog order by postTime desc";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Blog blog = new Blog();
                blog.setBlogId(resultSet.getInt("blogId"));
//                System.out.println(blog.getBlogId());
                blog.setTitle(resultSet.getString("title"));
                String content = resultSet.getString("content");
                if(content.length()>100) {
                    content = content.substring(0,100) + "...";
                }
                blog.setContent(content);
                blog.setPostTime(resultSet.getTimestamp("postTime"));
                blog.setUserId(resultSet.getInt("userId"));
                blogList.add(blog);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return blogList;
    }

    //根据博客ID显示博客
    public static Blog selectOne(int blogid) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from blog where blogId = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,blogid);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                Blog blog = new Blog();
                blog.setBlogId(resultSet.getInt("blogId"));
                blog.setTitle(resultSet.getString("title"));
                blog.setContent(resultSet.getString("content"));
                blog.setPostTime(resultSet.getTimestamp("postTime"));
                blog.setUserId(resultSet.getInt("userId"));
                return blog;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return null;
    }
    public static void delete(int blogid) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "delete from blog where blogId = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,blogid);
            int ret = preparedStatement.executeUpdate();
            if(1 == ret) {
                System.out.println("删除成功!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,null);
        }

    }
    //按userId查询博客数量
    public static int userblogcount(int userId) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select count(*) from blog where userId = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,userId);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()) {
                int count = resultSet.getInt(1);
                return count;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return -1;
    }
}
